package project.ccso.top.util;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;

import com.mchange.v2.c3p0.ComboPooledDataSource;

/**
 * 数据库连接的工具类
 * @author aibinxiao
 * @date 2018年8月16日 下午5:47:41
 */
public class DBUtil {
	
	private Connection connection = null;
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;
	private int intResult = -1;

	public DBUtil() {
		open();
	}
	
	/**
	 * 打开连接
	 */
	public void open() {
		try {
			// 2.打开数据库连接
			ComboPooledDataSource cpds = new ComboPooledDataSource();
			connection = cpds.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 更新操作
	 * @param sql
	 * @param objects
	 * @return
	 */
	@SuppressWarnings("finally")
	public int executeUpdate(String sql, Object[] objects) {
		try {
			preparedStatement = connection.prepareStatement(sql);
			if(objects != null) {
				setPreparedStatement(objects);
			}
			intResult = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			intResult = -1;
		} finally {
			return intResult;
		}
	}
	
	/**
	 * 条件查询
	 * @param sql
	 * @param objects
	 * @return
	 */
	@SuppressWarnings("finally")
	public ResultSet executeQuery(String sql, Object[] objects) {
		try {
			preparedStatement = connection.prepareStatement(sql);
			if(objects != null) {
				setPreparedStatement(objects);
			}
			resultSet = preparedStatement.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
			resultSet = null;
		}finally {
			return resultSet;
		}
	}
	
	/**
	 * 对于已经初始化完毕的preparedStatement进行参数赋值
	 * @param objects
	 */
	public void setPreparedStatement(Object[] objects) {
		for (int i = 0; i < objects.length; i++) {
			try {
				preparedStatement.setObject(i+1, objects[i]);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 关闭连接资源
	 */
	public void close() {
		try {
			if (resultSet != null) {
				resultSet.close();
			}
			if (preparedStatement != null) {
				preparedStatement.close();
			}
			if (connection != null) {
				connection.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

	/**
	 * 查询全部
	 * @param sql
	 * @return
	 */
	@SuppressWarnings("finally")
	public ResultSet executeQuery(String sql) {
		try {
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
			resultSet = null;
		}finally {
			return resultSet;
		}
	}
	
	/**
	 * 更新操作
	 * @param sql
	 * @param objects
	 * @return
	 */
	@SuppressWarnings("finally")
	public int executeUpdate(String sql, List<Object> objects) {
		try {
			preparedStatement = connection.prepareStatement(sql);
			if(objects != null && objects.size() > 0) {
				setPreparedStatement(objects);
			}
			intResult = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			intResult = -1;
		} finally {
			return intResult;
		}
	}
	
	/**
	 * 条件查询
	 * @param sql
	 * @param objects
	 * @return
	 */
	@SuppressWarnings("finally")
	public ResultSet executeQuery(String sql, List<Object> objects) {
		try {
			preparedStatement = connection.prepareStatement(sql);
			if(objects != null) {
				setPreparedStatement(objects);
			}
			resultSet = preparedStatement.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
			resultSet = null;
		}finally {
			return resultSet;
		}
	}
	
	/**
	 * 对于已经初始化完毕的preparedStatement进行参数赋值
	 * @param objects
	 */
	public void setPreparedStatement(List<Object> objects) {
		for (int i = 0; i < objects.size(); i++) {
			try {
				preparedStatement.setObject(i+1, objects.get(i));
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

